*WHAT'S ACROSS THE BORDER?
*JHA, NEUMARK, AND RODRIGUEZ-LOPEZ

* THIS FILE CREATES THE AVERAGE-EARNINGS RANKING FOR 20-INDUSTRY CLASSIFICATION IN 1990

include directories_build.do


* Load data at the czone-state-industry level
use "`cbpdir'`s'cbp_national_1990_2016", clear

*drop _merge 
drop if year==.

merge m:1 sic87dd using "`cbpdir'`s'cbp_sic87dd_sic87ss_sic87xx_desc.dta"
keep if _merge==3
drop _merge 
drop if year==.

collapse (sum) emp-estab  (first)  sic87xx_desc, by(sic87xx year)

gen wageap=nom_ap/(2087*emp)
bys year: egen totemp=total(emp)
gen shareemp=emp/totemp
bys year: egen totest=total(estab)
gen shareest=estab/totest

sort year sic87xx

save "`cbpdir'`s'cbp_national_20ind_1990_2016.dta", replace

keep if year==1990 | year==2016

keep year sic87xx sic87xx_desc wageap shareemp 
reshape wide wageap shareemp, i(sic87xx) j(year)
egen wagerank90=rank(wageap1990), track
egen wagerank16=rank(wageap2016), track
order sic87xx sic87xx_desc shareemp1990 wageap1990 wagerank90 shareemp2016 wageap2016 wagerank16
sort wagerank90

keep sic87xx wagerank90
save "`cbpdir'`s'cbp_sic87xx_wagerank.dta", replace



